﻿using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Text;
using KZ.Utilities;

namespace KZ.Express.H.DAL
{
    public class DataAccessHelper
    {
        static SqlConnection _conn;
        static SqlTransaction _trans;

        #region Constructors

        public DataAccessHelper()
        {
            try
            {
                if (_conn == null)
                {
                    ConnectionStringSettings conn = ConfigurationManager.ConnectionStrings["Default"];
                    Utility._CONNECT = conn.ConnectionString;

                    _conn = new SqlConnection(Utility._CONNECT);
                    _conn.Open();
                    _conn.Close();
                }
            }
            catch (Exception ex)
            {
                LogDAL.LogEx("BLM.ERR.Configuration", ex);
                throw new DataAccessException("Không tìm thấy chuỗi kết nối trong file config. Bạn hãy kiểm tra lại các thông số trong file config");
            }
        }

        #endregion

        #region Public Methods

        public SqlParameter[] CreateParameters(int number)
        {
            SqlParameter[] sqlParameters = new SqlParameter[number];

            for (int i = 0; i < number; i++)
                sqlParameters[i] = new SqlParameter();

            return sqlParameters;
        }

        #region Transaction

        public void BeginTransaction()
        {
            if (_conn == null)
            {
                LogDAL.LogEx("BLM.ERR.Configuration", new Exception("Connection is not initialized"));
                throw new DataAccessException("Kết nỗi chưa được tạo");
            }
            else if (_conn.State == ConnectionState.Closed)
                _conn.Open();

            if (_trans == null)
                _trans = _conn.BeginTransaction();
        }

        public void CommitTransaction()
        {
            try
            {
                if (_trans != null)
                    _trans.Commit();
            }
            finally
            {
                if (_trans != null)
                    _trans.Dispose();
                _conn.Close();
                _trans = null;
            }
        }

        public void RollbackTransaction()
        {
            try
            {
                if (_trans != null)
                    _trans.Rollback();
            }
            finally
            {
                if (_trans != null)
                    _trans.Dispose();
                _conn.Close();
                _trans = null;
            }
        }

        #endregion Transaction

        #region Execute Query

        public int ExecuteNonQuery(string query, params DbParameter[] parameters)
        {
            try
            {
                if (_conn.State == ConnectionState.Closed)
                    _conn.Open();

                SqlCommand sqlCommand = new SqlCommand(query, _conn);
                sqlCommand.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                    sqlCommand.Parameters.AddRange(parameters);

                //// check transaction is exist
                if (_trans != null)
                    sqlCommand.Transaction = _trans;

                return sqlCommand.ExecuteNonQuery();
            }
            finally
            {
                //// close connection automatically if transaction is not exist
                if (_trans == null) { _conn.Close(); }
            }
        }

        public int ExecuteNonQueryWithoutTrans(string query, params DbParameter[] parameters)
        {
            try
            {
                if (_conn.State == ConnectionState.Closed)
                    _conn.Open();

                SqlCommand sqlCommand = new SqlCommand(query, _conn);
                sqlCommand.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                    sqlCommand.Parameters.AddRange(parameters);

                return sqlCommand.ExecuteNonQuery();
            }
            finally
            {
                //// close connection automatically if transaction is not exist
                if (_trans == null) { _conn.Close(); }
            }
        }

        public IDataReader ExecuteReader(string query, params DbParameter[] parameters)
        {
            try
            {
                if (_conn.State == ConnectionState.Closed)
                    _conn.Open();

                SqlCommand sqlCommand = new SqlCommand(query, _conn);
                sqlCommand.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                    sqlCommand.Parameters.AddRange(parameters);

                //// check transaction is exist
                if (_trans != null)
                    sqlCommand.Transaction = _trans;

                return sqlCommand.ExecuteReader();
            }
            finally
            {
                //// close connection automatically if transaction is not exist
                if (_trans == null) { _conn.Close(); }
            }
        }

        public DataTable ExecuteDataTable(string query, params DbParameter[] parameters)
        {
            try
            {
                if (_conn.State == ConnectionState.Closed)
                    _conn.Open();

                SqlCommand sqlCommand = new SqlCommand(query, _conn);
                sqlCommand.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                    sqlCommand.Parameters.AddRange(parameters);

                //// check transaction is exist
                if (_trans != null)
                    sqlCommand.Transaction = _trans;

                DataTable dt = new DataTable();
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                sqlDataAdapter.Fill(dt);

                return dt;
            }
            finally
            {
                //// close connection automatically if transaction is not exist
                if (_trans == null) { _conn.Close(); }
            }
        }

        #endregion Execute Query

        #endregion
    }
}